package com.rykj.qxj.test.jdbcTemplate;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.rykj.qxj.util.SignUtil;

public class Page{
	
	private static Logger logger = LoggerFactory.getLogger(SignUtil.class);
	public static final int PAGE_SIZE = 10;
	 //一页显示的记录数
	  private int pageSize; 
	 //记录总数
	  private int totalRows; 
	 //总页数
	  private int totalPages; 
	 //当前页码
	  private int currentPage; 
	  //起始行数
	  private int startIndex;
	  //结束行数
	  private int lastIndex;
	  //结果集存放List
	  private List resultList;
	  private NamedParameterJdbcTemplate namedParameterJdbcTemplate ;
	  /**分页构造函数
	   * @param sql 根据传入的sql语句得到一些基本分页信息
	   * @param currentPage 当前页
	   * @param pageSize 每页显示记录数
	   * @param NamedParameterJdbcTemplate namedParameterJdbcTemplate实例
	   */
	  public Page(String sql,int currentPage,int pageSize,NamedParameterJdbcTemplate namedParameterJdbcTemplate,Map<String,Object> paramMap){
	    if(namedParameterJdbcTemplate == null){
	      throw new IllegalArgumentException("com.deity.ranking.util.Page.namedParameterJdbcTemplate is null,please initial it first. ");
	    }else if(sql == null || sql.equals("")){
	      throw new IllegalArgumentException("com.deity.ranking.util.Page.sql is empty,please initial it first. ");
	    }
	    //设置每页显示记录数
	    setPageSize(pageSize);
	    //设置要显示的页数
	    setCurrentPage(currentPage);
	    //计算总记录数
	    /*StringBuffer totalSQL = new StringBuffer(" SELECT count(*) FROM ( ");
	    totalSQL.append(sql);
	    totalSQL.append(" ) totalTable ");*/
		  String[] strs = sql.toLowerCase().split("from");
		  strs[0] = "select count(*) from ";
		  String totalSQL = StringUtils.join(strs,"");
	    //给NamedParameterJdbcTemplate赋值
	    setNamedParameterJdbcTemplate(namedParameterJdbcTemplate);
	    //总记录数
	    setTotalRows(getNamedParameterJdbcTemplate().queryForObject(totalSQL, paramMap, Integer.class));
	    //计算总页数
	    setTotalPages();
	    //计算起始行数
	    setStartIndex();
	    //计算结束行数
	    setLastIndex();
	    System.out.println("lastIndex="+lastIndex);
	    //装入结果集
	    setResultList(namedParameterJdbcTemplate.queryForList(getMySQLPageSQL(sql,startIndex,pageSize), paramMap));
	  }
	    /**
		 * 构造MySQL数据分页SQL 
		 * @param queryString
		 * @param startIndex
		 * @param pageSize
		 * @return
		 */
		public String getMySQLPageSQL(String queryString, Integer startIndex, Integer pageSize)
		{
			String result = "";
			if (null != startIndex && null != pageSize)
			{
				result = queryString + " limit " + startIndex + "," + pageSize;
			} else if (null != startIndex && null == pageSize)
			{
				result = queryString + " limit " + startIndex;
			} else
			{
				result = queryString;
			}
			return result;
		}
	  public int getCurrentPage() {
	    return currentPage;
	  } 
 
	  public void setCurrentPage(int currentPage) {
	    this.currentPage = currentPage;
	  }
 
	  public int getPageSize() {
		return pageSize;
	  }
	  public void setPageSize(int pageSize) {
			this.pageSize = pageSize;
	  }
	  public List getResultList() {
	    return resultList;
	  }
 
	  public void setResultList(List resultList) {
	    this.resultList = resultList;
	  }
 
	  public int getTotalPages() {
	    return totalPages;
	  }
	 //计算总页数
	  public void setTotalPages() {
	    if(totalRows % pageSize == 0){
	      this.totalPages = totalRows / pageSize;
	    }else{
	      this.totalPages = (totalRows / pageSize) + 1;
	    }
	  }
 
	  public int getTotalRows() {
	    return totalRows;
	  }
 
	  public void setTotalRows(int totalRows) {
	    this.totalRows = totalRows;
	  }
 
	  public int getStartIndex() {
	    return startIndex;
	  }
 
	  public void setStartIndex() {
	    this.startIndex = (currentPage - 1) * pageSize;
	  }
 
	  public int getLastIndex() {
	    return lastIndex;
	  }
 
	 
	   
	 public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
		return namedParameterJdbcTemplate;
	}

	public void setNamedParameterJdbcTemplate(NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
		this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
	}
	//计算结束时候的索引
	  public void setLastIndex() {
	    System.out.println("totalRows="+totalRows);///////////
	    System.out.println("pageSize="+pageSize);///////////
	    if( totalRows < pageSize){
	      this.lastIndex = totalRows;
	    }else if((totalRows % pageSize == 0) || (totalRows % pageSize != 0 && currentPage < totalPages)){
	      this.lastIndex = currentPage * pageSize;
	    }else if(totalRows % pageSize != 0 && currentPage == totalPages){//最后一页
	      this.lastIndex = totalRows ;
	    }
	  }
	  
	  @Override
	public String toString() {
		return "Page [pageSize=" + pageSize + ", totalRows=" + totalRows + ", totalPages=" + totalPages
				+ ", currentPage=" + currentPage + ", startIndex=" + startIndex + ", lastIndex=" + lastIndex
				+ ", resultList=" + resultList + ", namedParameterJdbcTemplate=" + namedParameterJdbcTemplate + "]";
	}


	  public NamedParameterJdbcTemplate getNamedParameterJdbcTemplateByJdbcTemplate() {
		  DriverManagerDataSource dataSource=new DriverManagerDataSource();
	      dataSource.setDriverClassName("com.mysql.jdbc.Driver");
	      dataSource.setUrl("jdbc:mysql://localhost:3306/test");
	      dataSource.setUsername("root");
	      dataSource.setPassword("123");
	      JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource);
	      NamedParameterJdbcTemplate namedParameterJdbcTemplate=new NamedParameterJdbcTemplate(jdbcTemplate);
	      return namedParameterJdbcTemplate;
		  
	  }

	public static void main(String[] args) throws JsonProcessingException {
		  int currentPage=1;
		  int numPerPage=5;
		  DriverManagerDataSource dataSource=new DriverManagerDataSource();
	      dataSource.setDriverClassName("com.mysql.jdbc.Driver");
	      dataSource.setUrl("jdbc:mysql://localhost:3306/test");
	      dataSource.setUsername("root");
	      dataSource.setPassword("123");
	      
	      JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource);
	      NamedParameterJdbcTemplate namedParameterJdbcTemplate=new NamedParameterJdbcTemplate(jdbcTemplate);  
	      System.out.println("==============分页查询开始====================");
		  String sql="select * from student where name=:name";
	      Map<String,Object> paramMap=new HashMap<String, Object>();
	      paramMap.put("name", "张三");
		  Page page=new Page(sql, currentPage, numPerPage, namedParameterJdbcTemplate,paramMap);
		  System.out.println(page.toString());
		  System.out.println("==============分页查询结束====================");
		  
		  System.out.println("===============批量插入============================");
		  Map<String,Object> param1=new HashMap<String,Object>();
		  param1.put("name", "测试0417");
		  param1.put("age", 3);
		  param1.put("grade_id", 3);
		/*  Map<String,Object> param2=new HashMap<String,Object>();
		  param2.put("name", "测试4111354");
		  param2.put("age", 4);
		  param2.put("grade_id", 4);
		  param2.put("sex", 5);
		  List<Map<String,Object>> paramList=new ArrayList<Map<String,Object>>();
		  paramList.add(param1);
		  paramList.add(param2);*/
		  
		  ObjectMapper mapper=new ObjectMapper();
		  String resultJson = mapper.writeValueAsString(param1);
		  System.out.println("请求参数的格式:"+resultJson);
		  //[{"grade_id":1,"name":"测试1","age":11},{"grade_id":2,"name":"测试2","age":22}]
		  
		  String insetSql = "INSERT INTO student (name, age, grade_id) VALUES (:name,:age,:grade_id)";
		 // int[] updateCounts = namedParameterJdbcTemplate.batchUpdate(sql, batchValues.toArray(new Map[userList.size()]));
		  //SqlParameterSource[] beanSources  = SqlParameterSourceUtils.createBatch(param1.toArray());
		  //int[] batchUpdate = namedParameterJdbcTemplate.batchUpdate(insetSql, beanSources);
		  int update = namedParameterJdbcTemplate.update(insetSql, param1);
		  System.out.println("===================批量插入测试结束==============================");
		  
		 /* System.out.println("====================批量更新测试开始=====================================");
		  
		  String updateSql="UPDATE student SET name =:name,age=:age WHERE age>:age";
		  
		  
		  Map<String,Object> updateParam=new HashMap<String,Object>();
		  updateParam.put("name", "修改4111353年龄大于20");
		  updateParam.put("age", 20);
		  List<Map<String,Object>> updateParamList=new ArrayList<Map<String,Object>>();
		  updateParamList.add(updateParam);
		  SqlParameterSource[] updateBeanSources  = SqlParameterSourceUtils.createBatch(updateParamList.toArray());
		  int[] a=namedParameterJdbcTemplate.batchUpdate(updateSql, updateBeanSources);
		  System.out.println("====================批量更新测试结束====================================");*/
	}
}

